<?php
// +----------------------------------------------------------------------
// | Copyright © Sogu Technologies Co. Ltd 2018-2050 All rights reserved.
// +----------------------------------------------------------------------
// | File name: UserFollowDataController.php
// +----------------------------------------------------------------------
// | Author: liuju
// +----------------------------------------------------------------------
// | Description:  主要是查询用户放款前埋点数据
// +----------------------------------------------------------------------
// | Others: 其他内容的说明
// +----------------------------------------------------------------------
// | History: 修改历史记录列表，每条修改记录应包含修改日期、修改者及修改内容简述
// +----------------------------------------------------------------------
// | 1.Date: 2019-03-06 Author: liuju Modification: 创建
// +----------------------------------------------------------------------

namespace App\Http\Controllers\Backend\FollowData;


use App\Constant\UserFollowTypeCode;
use App\Http\Controllers\Controller;
use App\Models\UserFollow;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

class UserFollowDataController extends Controller
{

    public function __construct()
    {
        $this->middleware('auth:backend');
        $this->user = auth('backend')->user();
    }


    /**
     * Description: 获取可筛选节点
     * Author: cg
     * DateTime: 2019-03-11 10:40
     * @return mixed
     */
    public function getTypeList()
    {
        return success(UserFollowTypeCode::USER_FOLLOW_TYPE_CHOOSE);
    }

    /**
     * Description: 导出埋点数据流失率
     * Author: cg
     * DateTime: 2019-03-09 10:10
     * @param Request $request
     * @return mixed
     */
    public function exportRequestParams(Request $request)
    {
        $start_time = $request->get('start_time', '');      // 开始时间
        $end_time = $request->get('end_time', '');        // 结束时间
        $channel = $request->get('channel', '');         // 渠道id 多个用逗号分隔
        $flag = $request->get('flag', 'day');         // 获取类型,默认为day
        $type = $request->get('type', '');
        $page = $request->get('page', '1');
        $page_size = $request->get('page_size', '10');
        $sort_type = $request->get('sort_type', '');
        $is_up = $request->get('is_up', 2);            // 排序 1-升序 2-降序
        $channel_lock = $request->get('channel_lock', 2);   // 渠道开关 1-开启 2-关闭
        $isExport = $request->get('is_export', 0);
        if ($channel_lock == 2) $channel = '';  //过滤渠道
        if (!$start_time || !$end_time) {
            return failed('请选择开始结束时间');
        }

        //实例化用户总表,
        $userFollow = new UserFollow();
        $data = $userFollow->queryData($channel_lock, $flag, $start_time, $end_time, $channel, $type);

        empty($data) && $data = [];

        //排序
        if ($sort_type && $is_up == 2) {
            $names = array_column($data, $sort_type);
            array_multisort($names, SORT_DESC, $data);
        } elseif ($sort_type && $is_up == 1) {
            $names = array_column($data, $sort_type);
            array_multisort($names, SORT_ASC, $data);
        }

        // 导出excel
        if ($isExport) {
            return $this->exportData($data, $start_time, $end_time, $flag, $channel_lock);
        }

        // 分页统计
        $total = count($data);
        if ($page && $page_size && !$isExport) {
            $data = array_slice($data, ($page - 1) * $page_size, $page_size);
        }

        return success(["total" => $total, "list" => $data]);
    }



    /**
     * Description: 埋点,信用卡,芝麻信用,手机认证三个节点的停留时间统计,生成数据 excel export
     * @author liChunlong
     * DateTime: 2019-03-12 10:24
     * $data
     */
    public function maiDianTimeGapCalExcel(Request $request)
    {
        $begin_time = $request->get('start_time', '');
        $end_time = $request->get('end_time', '');
        if (!$begin_time || !$end_time) {
            return failed('请选择开始结束时间');
        }
        $sql = "SELECT  created_at,type,user_id,channel ";
        $sql .= " FROM `user_follows` ";
        $sql .= " WHERE CODE = 200 AND created_at >= '" . $begin_time . "' AND created_at <= '" . $end_time . "' AND type in (1012,1013,1014)";
        $sql .= " ORDER BY channel , user_id ";
        $data = DB::connection('backend')->select($sql);
        $user_data = [];
        foreach ($data as $k => $v) {
            $user_data[$v->user_id][] = $v;
        }
        //code为1013与code是1012的时间间隔数组
        $gap_1012_1013_arr = [];
        //code为1014与code是1013的时间间隔数组
        $gap_1013_1014_arr = [];
        $ret_data = [];
        foreach ($user_data as $k1 => $v1) {
            $crate_time_1012_has = false;
            $crate_time_1012_time = '';
            $crate_time_1013_has = false;
            $crate_time_1013_time = '';
            $crate_time_1014_has = false;
            $crate_time_1014_time = '';
            $cur_channel = '';
            foreach ($v1 as $k2 => $v2) {
                if ($v2->type == 1012) {
                    $crate_time_1012_has = true;
                    $crate_time_1012_time = $v2->created_at;
                }
                if ($v2->type == 1013) {
                    $crate_time_1013_has = true;
                    $crate_time_1013_time = $v2->created_at;
                }
                if ($v2->type == 1014) {
                    $crate_time_1014_has = true;
                    $crate_time_1014_time = $v2->created_at;
                }
                $cur_channel = $v2->channel;
            }
            $cur_user_time_gap_1012_1013 = 0;
            $cur_user_time_gap_1013_1014 = 0;
            if (!$crate_time_1012_has || !$crate_time_1014_has) {
                continue;
            }
            if (!$crate_time_1013_has) {
                continue;
            } else {
                if ($crate_time_1012_has) {
                    $cur_user_time_gap_1012_1013 = abs(strtotime($crate_time_1013_time) - strtotime($crate_time_1012_time));
                    array_push($gap_1012_1013_arr, $cur_user_time_gap_1012_1013);
                }
                if ($crate_time_1014_has) {
                    $cur_user_time_gap_1013_1014 = abs(strtotime($crate_time_1014_time) - strtotime($crate_time_1013_time));
                    array_push($gap_1013_1014_arr, $cur_user_time_gap_1013_1014);
                }
            }
            $gap_user_lst[] = [
                'user_id' => $k1,
                'channel' => $cur_channel,
                'cur_user_time_gap_1012_1013' => $cur_user_time_gap_1012_1013,
                'cur_user_time_gap_1013_1014' => $cur_user_time_gap_1013_1014
            ];
        }
//        var_dump($gap_user_lst);
        $this->exportMaiDianData($gap_user_lst);
        return false;
    }

    /**
     * Description: 埋点,信用卡,芝麻信用,手机认证三个节点的停留时间统计,excel export
     * @author liChunlong
     * DateTime: 2019-03-12 11:24
     * $data
     */
    public function exportMaiDianData($data)
    {
        ob_clean();
        if (!$data) {
            return failed('埋点统计缓存数据尚未生成，请等待');
        }
        $header = [
            '用户id', '渠道号', '信用卡和通讯录认证间隔(秒数)', '芝麻认证和通讯录认证间隔(秒数)',
        ];
        $spread = new Spreadsheet();
        $sheet = $spread->getActiveSheet();
        //设置header
        $i = 0;
        foreach ($header as $value) {
            $cellName = chr(65 + $i) . "1";
            $sheet->setCellValue($cellName, $value)->calculateColumnWidths();
            $sheet->getColumnDimension(chr(65 + $i))->setWidth('10');
            $i++;
        }
        $field = [
            'user_id', 'channel', 'cur_user_time_gap_1012_1013', 'cur_user_time_gap_1013_1014'
        ];
        //设置value
        $len = count($field);
        foreach ($data as $key => $item) {
            $row = 2 + ($key * 2);
            for ($i = 0; $i < $len; $i++) {
                $sheet->setCellValueExplicit(chr(65 + $i) . $row, $item[$field[$i]], DataType::TYPE_STRING);
            }
        }
        $writer = new Xlsx($spread);
        //$file_name = $start_time . '~' . $end_time . '-' . UserFollow::USER_FOLLOW_FLAG[$flag];
        $file_name = '埋点时间差';
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');//告诉浏览器输出07Excel文件
        header('Content-Disposition: attachment;filename="' . $file_name . '.xlsx"');//告诉浏览器输出浏览器名称
        header('Cache-Control: max-age=0');
        $writer->save('php://output');
        @ob_flush();
        exit;
    }


    public function exportData($data, $start_time, $end_time, $flag, $channel_lock)
    {
        ob_clean();
        if (!$data) {
            return failed('埋点统计缓存数据尚未生成，请等待');
        }
        if ($channel_lock == 1) {
            $header = [
                '统计开始时间', '统计结束时间', '渠道ID', '渠道名称', '落地页面PV', '落地页面UV', '注册人数', '登录人数', '申请借钱提交', '实名认证提交',
                '人脸识别提交', '个人信息提交', '储蓄卡绑定提交', '信用卡绑定提交', '手机实名提交', '芝麻信用', '页面认证提交', '确认申请'
            ];
        } else {
            $header = [
                '统计开始时间', '统计结束时间', '落地页面PV', '落地页面UV', '注册人数', '登录人数', '申请借钱提交', '实名认证提交',
                '人脸识别提交', '个人信息提交', '储蓄卡绑定提交', '信用卡绑定提交', '手机实名提交', '芝麻信用', '页面认证提交', '确认申请'
            ];
        }

        $spread = new Spreadsheet();
        $sheet = $spread->getActiveSheet();
        //设置header
        $i = 0;
        foreach ($header as $value) {
            $cellName = chr(65 + $i) . "1";
            $sheet->setCellValue($cellName, $value)->calculateColumnWidths();
            $sheet->getColumnDimension(chr(65 + $i))->setWidth('10');
            $i++;
        }

        if ($channel_lock == 1) {
            $field = [
                'start_statistics_time', 'end_statistics_time', 'channel', 'pv', 'uv', 'register_num', 'login_num', 'want_loan',
                'card_submit', 'face_submit', 'contact_submit', 'debit_card_submit', 'credit_card_submit',
                'phone_auth', 'zhima_auth', 'auth_submit', 'confirm_submit'
            ];
        } else {
            $field = [
                'start_statistics_time', 'end_statistics_time', 'pv', 'uv', 'register_num', 'login_num', 'want_loan',
                'card_submit', 'face_submit', 'contact_submit', 'debit_card_submit', 'credit_card_submit',
                'phone_auth', 'zhima_auth', 'auth_submit', 'confirm_submit'
            ];
        }


        //设置value
        $len = count($field);
        foreach ($data as $key => $item) {
            $row = 2 + ($key * 2);
            for ($i = 0; $i < $len; $i++) {
                $sheet->setCellValueExplicit(chr(65 + $i) . $row, $item[$field[$i]], DataType::TYPE_STRING);
                if ($i == 1) {
                    $sheet->setCellValueExplicit(chr(65 + $i) . ($row + 1), '流失率', DataType::TYPE_STRING);
                }
                if ($i == 4) {
                    $sheet->setCellValueExplicit(chr(65 + $i) . ($row + 1), $item['uv_loss_percent'], DataType::TYPE_STRING);
                }
                if ($i == 5) {
                    $sheet->setCellValueExplicit(chr(65 + $i) . ($row + 1), $item['register_num_loss_percent'], DataType::TYPE_STRING);
                }
                if ($i == 6) {
                    $sheet->setCellValueExplicit(chr(65 + $i) . ($row + 1), $item['login_loss_percent'], DataType::TYPE_STRING);
                }
                if ($i == 7) {
                    $sheet->setCellValueExplicit(chr(65 + $i) . ($row + 1), $item['want_loan_loss_percent'], DataType::TYPE_STRING);
                }
                if ($i == 8) {
                    $sheet->setCellValueExplicit(chr(65 + $i) . ($row + 1), $item['card_submit_loss_percent'], DataType::TYPE_STRING);
                }
                if ($i == 9) {
                    $sheet->setCellValueExplicit(chr(65 + $i) . ($row + 1), $item['face_submit_loss_percent'], DataType::TYPE_STRING);
                }
                if ($i == 10) {
                    $sheet->setCellValueExplicit(chr(65 + $i) . ($row + 1), $item['contact_submit_loss_percent'], DataType::TYPE_STRING);
                }
                if ($i == 11) {
                    $sheet->setCellValueExplicit(chr(65 + $i) . ($row + 1), $item['debit_card_submit_loss_page'], DataType::TYPE_STRING);
                }

                if ($i == 12) {
                    $sheet->setCellValueExplicit(chr(65 + $i) . ($row + 1), $item['credit_card_submit_loss_page'], DataType::TYPE_STRING);
                }

                if ($i == 13) {
                    $sheet->setCellValueExplicit(chr(65 + $i) . ($row + 1), $item['phone_auth_loss_percent'], DataType::TYPE_STRING);
                }
                if ($i == 14) {
                    $sheet->setCellValueExplicit(chr(65 + $i) . ($row + 1), $item['zhima_auth_loss_percent'], DataType::TYPE_STRING);
                }
                if ($i == 15) {
                    $sheet->setCellValueExplicit(chr(65 + $i) . ($row + 1), $item['auth_submit_loss_percent'], DataType::TYPE_STRING);
                }
                if ($i == 16) {
                    $sheet->setCellValueExplicit(chr(65 + $i) . ($row + 1), $item['confirm_submit_loss_percent'], DataType::TYPE_STRING);
                }

            }
        }
        $writer = new Xlsx($spread);
        $file_name = $start_time . '~' . $end_time . '-' . UserFollow::USER_FOLLOW_FLAG[$flag];
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');//告诉浏览器输出07Excel文件
        header('Content-Disposition: attachment;filename="' . $file_name . '.xlsx"');//告诉浏览器输出浏览器名称
        header('Cache-Control: max-age=0');
        $writer->save('php://output');
        @ob_flush();
        exit;
    }

}

